/*
 * Group:PrePool K9-357 
 * 8/2010
 */
package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import dto.LanguageDTO;

import org.apache.log4j.Logger;

import util.Controller;

/**
 *
 */
public class LanguagesDAO {
	public static Logger LOG = Logger.getLogger("dao.LanguagesDAO");

	/**
	 * @param name
	 *            dictionary's name
	 * @return true if successful,false if false
	 */
	public boolean addLanguage(String name) {
		name = name.trim();

		if (name == null) {
			return false;
		}
		if (name == "") {
			return false;
		}
		if (name.length() == 0) {
			return false;
		}

		boolean bOK = false;
		Connection cnn = null;
		PreparedStatement pstatCheck = null;
		CallableStatement cstatInsert = null;
		ResultSet rs = null;

		try {
			cnn = Controller.getConnection();
			pstatCheck = cnn
					.prepareStatement("select * from m_languages where m_name='"
							+ name + "'");
			rs = pstatCheck.executeQuery();
			if (rs.next() == false) {
				pstatCheck.close();
				cstatInsert = cnn.prepareCall("{call m_languages_insert(?)}");
				cstatInsert.setString(1, name);
				bOK = (cstatInsert.executeUpdate() == 1);
			}
		} catch (Exception ex) {
			LOG.error("addLanguage", ex);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstatCheck != null) {
					pstatCheck.close();
				}
				if (cstatInsert != null) {
					cstatInsert.close();
				}
				if (cnn != null) {
					cnn.close();
				}
			} catch (Exception exx) {
			}
		}
		return bOK;
	}

	/**
	 * remove language by its name
	 * 
	 * @param name
	 *            language's name
	 */
	public boolean removeLanguage(String name) {
		Connection cnn = null;
		PreparedStatement pstatsel = null;
		ResultSet rs = null;
		int langID = -1;

		try {
			cnn = Controller.getConnection();
			pstatsel = cnn
					.prepareStatement("select m_id from m_languages where m_name=N'"
							+ name + "'");
			rs = pstatsel.executeQuery();
			if (rs.next()) {
				langID = rs.getInt("m_id");
			}
			rs.close();
			pstatsel.close();
		} catch (Exception ex) {
			// LOG.error("removeLanguage(String)", ex);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstatsel != null) {
					pstatsel.close();
				}
				if (cnn != null) {
					cnn.close();
				}
			} catch (Exception exx) {
			}
		}
		if (langID != -1) {
			return this.removeLanguage(langID);
		} else {
			return false;
		}
	}

	/**
	 * remove language by its ID
	 * 
	 * @param langId
	 *            language's Id
	 * @return
	 */
	public boolean removeLanguage(int langId) {
		boolean bOK = false;
		Connection cnn = null;
		CallableStatement cstat = null;
		ResultSet rs = null;
		DictionariesDAO dictList = new DictionariesDAO();
		
		try {
			cnn = Controller.getConnection();
			cstat = cnn
					.prepareCall("select m_id from m_dictionaries where m_fromID="
							+ langId
							+ " or m_toID="
							+ langId
							+ " group by m_id");
			rs = cstat.executeQuery();

			while (rs.next()) {
				dictList.deleteDict(rs.getInt("m_id"));
			}
			
			rs.close();
			cstat.close();
			cstat = cnn.prepareCall("{call m_dictionaries_delete(?)}");
			cstat.setInt(1, langId);
			bOK = (cstat.executeUpdate() == 1);
		} catch (Exception ex) {
			// LOG.error("removeLanguage(int)", ex);
		} finally {
			try {
				if (cstat != null) {
					cstat.close();
				}
				if (cnn != null) {
					cnn.close();
				}
			} catch (Exception exx) {
			}
		}
		return bOK;
	}

	/**
	 * @return
	 */
	public List<LanguageDTO> getLangList() {
		List<LanguageDTO> list = new ArrayList<LanguageDTO>();
		Connection cnn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		
		try {
			cnn = Controller.getConnection();
			pstat = cnn.prepareStatement("select m_id,m_name from m_languages");
			rs = pstat.executeQuery();
			while (rs.next()) {
				LanguageDTO dt = new LanguageDTO();
				dt.setId(rs.getInt("m_id"));
				dt.setName(rs.getString("m_name"));
				list.add(dt);
			}
		} catch (Exception ex) {
			// LOG.error("getLangList", ex);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstat != null) {
					pstat.close();
				}
				if (cnn != null) {
					cnn.close();
				}
			} catch (Exception ex) {
			}
		}
		return list;
	}

	/**
	 * @param fromLangId
	 * @return
	 */
	public List<LanguageDTO> getAvailableLanguage(int fromLangId) {
		Connection cnn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<LanguageDTO> list = new ArrayList<LanguageDTO>();
		
		try {
			cnn = Controller.getConnection();
			pstat = cnn
					.prepareStatement("select * from m_languages where m_id in (select m_toID from m_dictionaries "
							+ "where m_fromID = ?)");
			pstat.setInt(1, fromLangId);
			rs = pstat.executeQuery();

			while (rs.next()) {
				LanguageDTO language = new LanguageDTO();
				language.setId(rs.getInt("m_id"));
				language.setName(rs.getString("m_name"));
				list.add(language);
			}
		} catch (Exception e) {
			// TODO: handle exception
		}

		finally {
			try {
				if (rs != null) {
					rs.close();
				}

				if (pstat != null) {
					pstat.close();
				}

				if (cnn != null) {
					cnn.close();
				}
			} catch (Exception e) {
				// TODO: handle exception
			}
		}
		return list;
	}
}
